/*
 Navicat Premium Data Transfer

 Source Server         : 本地
 Source Server Type    : MySQL
 Source Server Version : 80027 (8.0.27)
 Source Host           : 127.0.0.1:3306
 Source Schema         : pet

 Target Server Type    : MySQL
 Target Server Version : 80027 (8.0.27)
 File Encoding         : 65001

 Date: 24/11/2022 18:03:36
*/

use mysql;
drop database if EXISTS pet;
create database pet;
use pet;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for breed
-- ----------------------------
DROP TABLE IF EXISTS `breed`;
CREATE TABLE `breed` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '宠物品种ID',
  `speciesid` int NOT NULL COMMENT '宠物类型ID',
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '宠物品种名称',
  PRIMARY KEY (`id`),
  KEY `speciesid` (`speciesid`),
  CONSTRAINT `breed_ibfk_speciesid` FOREIGN KEY (`speciesid`) REFERENCES `species` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of breed
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for pet
-- ----------------------------
DROP TABLE IF EXISTS `pet`;
CREATE TABLE `pet` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '宠物ID',
  `breedid` int NOT NULL COMMENT '宠物品种ID',
  `nickname` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '宠物昵称',
  `age` int NOT NULL COMMENT '年龄',
  `sex` int NOT NULL COMMENT '性别(1:雄性;2:雌性)',
  `remrk` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `createtime` datetime NOT NULL COMMENT '创建时间',
  `createuser` int NOT NULL COMMENT '创建人id',
  `owner` int DEFAULT NULL COMMENT '领养人id',
  `ownertime` datetime DEFAULT NULL COMMENT '领养时间',
  PRIMARY KEY (`id`),
  KEY `breedid` (`breedid`),
  CONSTRAINT `pet_ibfk_breedid` FOREIGN KEY (`breedid`) REFERENCES `breed` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of pet
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for species
-- ----------------------------
DROP TABLE IF EXISTS `species`;
CREATE TABLE `species` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '宠物类型ID',
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '宠物类型名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of species
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '账户ID',
  `role` int NOT NULL COMMENT '角色(1:管理员;2:会员)',
  `mobile` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '登录手机号(唯一性约束)',
  `password` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
  `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '账户名称',
  `regtime` datetime NOT NULL COMMENT '注册时间',
  `logintime` datetime DEFAULT NULL COMMENT '登录时间',
  `status` int NOT NULL COMMENT '账户状态(1:正常 2:注销)',
  `enable` int NOT NULL COMMENT '是否启用(1:启用 2:禁用)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

-- 以下为初始化数据
INSERT INTO `user` (`role`, `mobile`, `password`, `name`, `regtime`, `logintime`, `status`, `enable`) VALUES (1, '15802908989', '111', '管理员', '2022-11-27 11:41:33', '2022-11-27 11:41:36', 1, 1);
insert into species(name)  values ('宠物狗');
insert into species(name)  values ('宠物猫');
insert into breed(speciesid, name) values (1,'哈士奇');
insert into breed(speciesid, name) values (2,'波斯猫');
insert into pet(breedid, nickname, age, sex, remrk, createtime, createuser) values (1,'旺财',1,1,'这个家伙很懒！',now(),1);
insert into pet(breedid, nickname, age, sex, remrk, createtime, createuser) values (2,'翠花',1,2,'这个家伙很懒！',now(),1);